package com.example.work.check;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.mapper.*;
import com.example.work.Excel.AssetEntity;
import com.example.work.Excel.AssetReportZhiwang;
import com.example.work.Excel.DateUtils;
import com.example.work.Excel.WebAssetXlsx;
import com.example.work.entiy.DivisionAssetInfoCopy;
import com.example.work.entiy.DivisionAssetInfoWithIpPorts;
import com.example.work.entiy.SystemDomain;
import com.example.work.entiy.ranklog.*;
import com.example.work.euums.DeviceCategory;
import com.example.work.euums.ITResourceType;
import com.example.work.utils.AssetIdGenerator;
import com.example.work.utils.SqlGenerator;
import com.example.work.utils.UniversalSqlGenerator;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.function.Supplier;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 资产数据比对服务类
 * 负责将Excel中的资产数据与数据库中的数据进行比对
 */
@Service
public class AssetComparisonService {


    @Autowired
    private DivisionAssetInfoMapper divisionAssetInfoMapper;

    @Autowired
    private SystemDomainMapper systemDomainMapper;

    private final MockDatabaseService mockDatabaseService;
    @Autowired
    private DivisionAssetInfoCopyMapper dividerAssetInfoCopyMapper;

    @Autowired
    private BusinessRankLogBasisWebMapper businessRankMapper;



    @Autowired
    private BusinessRankLogSystemBasisWebCardDataMapper businessRankLogSystemBasisWebCardDataMapper;


    public AssetComparisonService() {
        this.mockDatabaseService = new MockDatabaseService();
    }
    
    public AssetComparisonService(MockDatabaseService mockDatabaseService) {
        this.mockDatabaseService = mockDatabaseService;
    }
    
    /**
     * 比对Excel数据与数据库数据
     * @param assetImportService 已导入Excel数据的服务实例
     * @return 比对结果
     */
    public ComparisonResult compareAssets(AssetImportService assetImportService) {
        ComparisonResult result = new ComparisonResult();
        
        // 获取数据库中的所有资产数据
/*        List<DivisionAssetInfoWithIpPorts> databaseAssets = divisionAssetInfoMapper.selectAssetInfoWithIpPorts();
        result.setTotalDatabaseRecords(databaseAssets.size());
        
        // 将数据库数据按匹配关键字组织成Map
        Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap = databaseAssets.stream()
                .collect(Collectors.toMap(
                        DivisionAssetInfoWithIpPorts::createMatchKey,
                    asset -> asset,
                    (existing, replacement) -> existing // 如果有重复的key，保留第一个
                ));*/
        
        Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap = new HashMap<>();
        System.out.println("数据库中共有 " + databaseAssetMap.size() + " 条资产记录");

        List<SystemDomain> systemDomains = systemDomainMapper.selectList(new LambdaQueryWrapper<>());
        // 转换为Map<String, String>，key=domainName, value=regionCode
        Map<String, String> domainMap = systemDomains.stream()
                .collect(Collectors.toMap(
                        SystemDomain::getDomainName,    // key映射器：获取domainName
                        SystemDomain::getRegionCode,    // value映射器：获取regionCode
                        (existing, replacement) -> existing  // 处理重复键的策略（保留现有值）
                ));
        System.out.println("所在城市表中 " + domainMap.size() + " 条资产记录");
        // 处理各类型的Excel数据
        int totalExcelRecords = 0;

        List<AssetReportZhiwang> assetReportZhiwangList = assetImportService.getAssetReportZhiwangList();
        // 将List转换为Map，key为assetId，value为对象
        Map<String, AssetReportZhiwang> assetMap = assetReportZhiwangList.stream()
                .collect(Collectors.toMap(
                        AssetReportZhiwang::getAssetId,
                        asset -> asset,
                        (existing, replacement) -> replacement
                ));
        System.out.println("全部文件中有 " + assetMap.size() + " 条资产记录");

        totalExcelRecords += processAssetList(assetImportService.getDatabaseAssetList(), 
                AssetTypeEnum.DATABASE, databaseAssetMap, result,domainMap,assetMap);
        totalExcelRecords += processAssetList(assetImportService.getServerAssetList(), 
                AssetTypeEnum.SERVER, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getWebAssetList(), 
                AssetTypeEnum.WEB, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getMiddlewareAssetList(), 
                AssetTypeEnum.MIDDLEWARE, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getLoadBalancerAssetList(), 
                AssetTypeEnum.LOAD_BALANCER, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getOtherApplicationSoftwareAssetList(), 
                AssetTypeEnum.OTHER_APPLICATION_SOFTWARE, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getOtherAssetList(), 
                AssetTypeEnum.OTHER, databaseAssetMap, result, domainMap, assetMap);

        totalExcelRecords += processAssetList(assetImportService.getFirewallList(),
                AssetTypeEnum.FIREWALL, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getSecurityProductList(),
                AssetTypeEnum.SECURITY_PRODUCT, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getRouterList(),
                AssetTypeEnum.ROUTER, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getSupplyChainList(),
                AssetTypeEnum.SUPPLY_CHAIN, databaseAssetMap, result, domainMap, assetMap);
        totalExcelRecords += processAssetList(assetImportService.getSwitchList(),
                AssetTypeEnum.SWITCH, databaseAssetMap, result, domainMap, assetMap);

        result.setTotalExcelRecords(totalExcelRecords);
        
        System.out.println("比对完成: Excel总记录 " + totalExcelRecords + ", 匹配成功 " + 
                result.getMatchedCount() + ", 需要插入 " + result.getInsertCount() + 
                ", 无效记录 " + result.getInvalidCount());
        
        return result;
    }

    public ComparisonResult compareAssets2(AssetImportService assetImportService) {
        ComparisonResult result = new ComparisonResult();

        // 获取数据库中的所有资产数据
/*        List<DivisionAssetInfoWithIpPorts> databaseAssets = divisionAssetInfoMapper.selectAssetInfoWithIpPorts();
        result.setTotalDatabaseRecords(databaseAssets.size());

        // 将数据库数据按匹配关键字组织成Map
        Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap = databaseAssets.stream()
                .collect(Collectors.toMap(
                        DivisionAssetInfoWithIpPorts::createMatchKey,
                    asset -> asset,
                    (existing, replacement) -> existing // 如果有重复的key，保留第一个
                ));*/

        Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap = new HashMap<>();
        System.out.println("数据库中共有 " + databaseAssetMap.size() + " 条资产记录");

        List<SystemDomain> systemDomains = systemDomainMapper.selectList(new LambdaQueryWrapper<>());
        // 转换为Map<String, String>，key=domainName, value=regionCode
        Map<String, String> domainMap = systemDomains.stream()
                .collect(Collectors.toMap(
                        SystemDomain::getDomainName,    // key映射器：获取domainName
                        SystemDomain::getRegionCode,    // value映射器：获取regionCode
                        (existing, replacement) -> existing  // 处理重复键的策略（保留现有值）
                ));
        System.out.println("所在城市表中 " + domainMap.size() + " 条资产记录");
        // 处理各类型的Excel数据
        int totalExcelRecords = 0;

        List<AssetReportZhiwang> assetReportZhiwangList = assetImportService.getAssetReportZhiwangList();

        totalExcelRecords += processAssetList2(assetReportZhiwangList,
                AssetTypeEnum.All, databaseAssetMap, result,domainMap);


        result.setTotalExcelRecords(totalExcelRecords);

        System.out.println("比对完成: Excel总记录 " + totalExcelRecords + ", 匹配成功 " +
                result.getMatchedCount() + ", 需要插入 " + result.getInsertCount() +
                ", 无效记录 " + result.getInvalidCount());

        return result;
    }

    /**
     * 直接更改copy表数据
     * @param assetImportService
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ComparisonResult compareAssets3(AssetImportService assetImportService) {

        List<BusinessRankLogBasisWeb> businessRankLogBasisWebs = businessRankMapper.selectBasisList();
        // 根据IpAddress分组，并将RelaId合并为逗号分隔的字符串
        Map<String, String> ipToRelaIdsMap = businessRankLogBasisWebs.stream()
                .filter(web -> web.getIpAddress() != null && !web.getIpAddress().isEmpty()) // 过滤掉IP为空的记录
                .collect(Collectors.groupingBy(
                        BusinessRankLogBasisWeb::getIpAddress, // 分组键：IP地址
                        Collectors.mapping(
                                BusinessRankLogBasisWeb::getId, // 映射：获取RelaId
                                Collectors.joining(",") // 合并：用逗号连接所有RelaId
                        )
                ));
        System.out.println("ipToRelaIdsMap = " + ipToRelaIdsMap.size());
        List<BusinessRankLogBasisWeb> businessRankLogBasisWebHistory =  businessRankMapper.selectBasisHistoryList();
        Map<String, String> ipToRelaIdsMapHistory = businessRankLogBasisWebHistory.stream()
                .filter(web -> web.getIpAddress() != null && !web.getIpAddress().isEmpty()) // 过滤掉IP为空的记录
                .collect(Collectors.groupingBy(
                        BusinessRankLogBasisWeb::getIpAddress, // 分组键：IP地址
                        Collectors.mapping(
                                BusinessRankLogBasisWeb::getId, // 映射：获取RelaId
                                Collectors.joining(",") // 合并：用逗号连接所有RelaId
                        )
                ));
        System.out.println("businessRankLogBasisWebHistory = " + ipToRelaIdsMapHistory.size());



        LambdaQueryWrapper<DivisionAssetInfoCopy> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeRight(DivisionAssetInfoCopy::getReportAssetType, "06")
                .or()
                .likeRight(DivisionAssetInfoCopy::getReportAssetType, "07");
        List<DivisionAssetInfoCopy> divisionAssetInfoCopies = dividerAssetInfoCopyMapper.selectList(queryWrapper);
        System.out.println("divisionAssetInfoCopies = " + divisionAssetInfoCopies.size());
        for (DivisionAssetInfoCopy divisionAssetInfoCopy : divisionAssetInfoCopies) {
            String assetIp = divisionAssetInfoCopy.getAssetIp();
            if (ipToRelaIdsMap.containsKey(assetIp)) {
                String relaIds = ipToRelaIdsMap.get(assetIp);
                divisionAssetInfoCopy.setNowAssetId(relaIds);
            }
            if (ipToRelaIdsMapHistory.containsKey(assetIp)) {
                String relaIds = ipToRelaIdsMapHistory.get(assetIp);
                divisionAssetInfoCopy.setHistoryAssetId(relaIds);
            }
            dividerAssetInfoCopyMapper.updateById(divisionAssetInfoCopy);
        }


        return null;
    }

    @Transactional(rollbackFor = Exception.class)
    public ComparisonResult compareAssets4(AssetImportService assetImportService) {


        List<BusinessRankLogSystemBasisWebCardWithCardData> businessRankLogSystemBasisWebCardWithCardData = businessRankLogSystemBasisWebCardDataMapper.selectCardWithCardData();
        Map<String, String> basisWebCardDataMap =  businessRankLogSystemBasisWebCardWithCardData.stream()
                .filter(web -> web.getWebCard() != null && !web.getWebCard().isEmpty()) // 过滤掉IP为空的记录
                .collect(Collectors.groupingBy(
                        BusinessRankLogSystemBasisWebCardWithCardData::getWebCard, // 分组键：IP地址
                        Collectors.mapping(
                                BusinessRankLogSystemBasisWebCardWithCardData::getId, // 映射：获取RelaId
                                Collectors.joining(",") // 合并：用逗号连接所有RelaId
                        )
                ));
        System.out.println("basisWebCardDataMap = " + basisWebCardDataMap.size());

        List<BusinessRankLogSystemBasisWebCardWithCardData> businessRankLogSystemBasisWebCardWithCardDataHistory = businessRankLogSystemBasisWebCardDataMapper.selectCardWithCardDataHistory();
        Map<String, String> basisWebCardDataMapHistory =  businessRankLogSystemBasisWebCardWithCardDataHistory.stream()
                .filter(web -> web.getWebCard() != null && !web.getWebCard().isEmpty()) // 过滤掉IP为空的记录
                .collect(Collectors.groupingBy(
                        BusinessRankLogSystemBasisWebCardWithCardData::getWebCard, // 分组键：IP地址
                        Collectors.mapping(
                                BusinessRankLogSystemBasisWebCardWithCardData::getId, // 映射：获取RelaId
                                Collectors.joining(",") // 合并：用逗号连接所有RelaId
                        )
                ));
        System.out.println("basisWebCardDataMapHistory = " + basisWebCardDataMapHistory.size());


        LambdaQueryWrapper<DivisionAssetInfoCopy> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeRight(DivisionAssetInfoCopy::getReportAssetType, "01")
                .or()
                .likeRight(DivisionAssetInfoCopy::getReportAssetType, "02").or().likeRight(DivisionAssetInfoCopy::getReportAssetType, "03");
        List<DivisionAssetInfoCopy> divisionAssetInfoCopies = dividerAssetInfoCopyMapper.selectList(queryWrapper);
        System.out.println("divisionAssetInfoCopies = " + divisionAssetInfoCopies.size());
        for (DivisionAssetInfoCopy divisionAssetInfoCopy : divisionAssetInfoCopies) {
            String assetIp = divisionAssetInfoCopy.getAssetIp();
            if (basisWebCardDataMap.containsKey(assetIp)) {
                String relaIds = basisWebCardDataMap.get(assetIp);
                divisionAssetInfoCopy.setNowAssetId(relaIds);
            }
            if (basisWebCardDataMapHistory.containsKey(assetIp)) {
                String relaIds = basisWebCardDataMapHistory.get(assetIp);
                divisionAssetInfoCopy.setHistoryAssetId(relaIds);
            }
            dividerAssetInfoCopyMapper.updateById(divisionAssetInfoCopy);
        }


        return null;
    }

    @Transactional(rollbackFor = Exception.class)
    public ComparisonResult compareAssets5(AssetImportService assetImportService) {

        // 现有数据
        List<RankLogAssetInfoBasisWebBase> currentList = businessRankMapper.selectRankAssectWebCard();

        // 历史数据
        List<RankLogAssetInfoBasisWebBase> historyList = businessRankMapper.selectRankAssectWebCardHistroy();

        // 查询 DivisionAssetInfoCopy
        LambdaQueryWrapper<DivisionAssetInfoCopy> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeRight(DivisionAssetInfoCopy::getReportAssetType, "06")
                .or().likeRight(DivisionAssetInfoCopy::getReportAssetType, "07");
        List<DivisionAssetInfoCopy> divisionList = dividerAssetInfoCopyMapper.selectList(queryWrapper);

        for (DivisionAssetInfoCopy division : divisionList) {
            String reportAssetType = division.getReportAssetType();

            // ==== 获取匹配 ID 集 ====
            Set<String> matchedNowIds = currentList.stream()
                    .filter(item -> Objects.equals(item.getReportAssetType(), reportAssetType))
                    .map(item -> String.valueOf(item.getId()))
                    .collect(Collectors.toSet());

            Set<String> matchedHistoryIds = historyList.stream()
                    .filter(item -> Objects.equals(item.getReportAssetType(), reportAssetType))
                    .map(item -> String.valueOf(item.getId()))
                    .collect(Collectors.toSet());

            // ==== 取交集：原有 ID ∩ 匹配 ID ====
            Set<String> finalNowIds = new LinkedHashSet<>();
            if (division.getNowAssetId() != null && !division.getNowAssetId().trim().isEmpty()) {
                Set<String> existingNowIds = Arrays.stream(division.getNowAssetId().split(","))
                        .map(String::trim)
                        .collect(Collectors.toSet());
                finalNowIds.addAll(existingNowIds);
                finalNowIds.retainAll(matchedNowIds); // 取交集
            }

            Set<String> finalHistoryIds = new LinkedHashSet<>();
            if (division.getHistoryAssetId() != null && !division.getHistoryAssetId().trim().isEmpty()) {
                Set<String> existingHistoryIds = Arrays.stream(division.getHistoryAssetId().split(","))
                        .map(String::trim)
                        .collect(Collectors.toSet());
                finalHistoryIds.addAll(existingHistoryIds);
                finalHistoryIds.retainAll(matchedHistoryIds); // 取交集
            }

            // ==== 更新数据库 ====
            division.setTypeNowAssetId(String.join(",", finalNowIds));
            division.setTypeHistoryAssetId(String.join(",", finalHistoryIds));
            dividerAssetInfoCopyMapper.updateById(division);
        }

        return null;
    }
    @Transactional(rollbackFor = Exception.class)
    public ComparisonResult compareAssets6(AssetImportService assetImportService) {

        // 现有数据
        List<RankLogAssetInfoSystemBasisWebCardBase> currentList =
                businessRankLogSystemBasisWebCardDataMapper.selectRankAssectWebCard();

        // 历史数据
        List<RankLogAssetInfoSystemBasisWebCardBase> historyList =
                businessRankLogSystemBasisWebCardDataMapper.selectRankAssectWebCardHistroy();

        // 查询 DivisionAssetInfoCopy
        LambdaQueryWrapper<DivisionAssetInfoCopy> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeRight(DivisionAssetInfoCopy::getReportAssetType, "01")
                .or().likeRight(DivisionAssetInfoCopy::getReportAssetType, "02")
                .or().likeRight(DivisionAssetInfoCopy::getReportAssetType, "03");
        List<DivisionAssetInfoCopy> divisionList = dividerAssetInfoCopyMapper.selectList(queryWrapper);

        for (DivisionAssetInfoCopy division : divisionList) {
            String reportAssetType = division.getReportAssetType();

            // ==== 获取匹配 ID 集 ====
            Set<String> matchedNowIds = currentList.stream()
                    .filter(item -> Objects.equals(item.getReportAssetType(), reportAssetType))
                    .map(item -> String.valueOf(item.getId()))
                    .collect(Collectors.toSet());

            Set<String> matchedHistoryIds = historyList.stream()
                    .filter(item -> Objects.equals(item.getReportAssetType(), reportAssetType))
                    .map(item -> String.valueOf(item.getId()))
                    .collect(Collectors.toSet());

            // ==== 取交集：原有 ID ∩ 匹配 ID ====
            Set<String> finalNowIds = new LinkedHashSet<>();
            if (division.getNowAssetId() != null && !division.getNowAssetId().trim().isEmpty()) {
                Set<String> existingNowIds = Arrays.stream(division.getNowAssetId().split(","))
                        .map(String::trim)
                        .collect(Collectors.toSet());
                finalNowIds.addAll(existingNowIds);
                finalNowIds.retainAll(matchedNowIds); // 取交集
            }

            Set<String> finalHistoryIds = new LinkedHashSet<>();
            if (division.getHistoryAssetId() != null && !division.getHistoryAssetId().trim().isEmpty()) {
                Set<String> existingHistoryIds = Arrays.stream(division.getHistoryAssetId().split(","))
                        .map(String::trim)
                        .collect(Collectors.toSet());
                finalHistoryIds.addAll(existingHistoryIds);
                finalHistoryIds.retainAll(matchedHistoryIds); // 取交集
            }

            division.setTypeNowAssetId(String.join(",", finalNowIds));
            division.setTypeHistoryAssetId(String.join(",", finalHistoryIds));
            // ==== 更新数据库 ====

            dividerAssetInfoCopyMapper.updateById(division);
        }

        return null;
    }




    public Map<String, BusinessRankLogBasisWeb> convertToIpPortMap(List<BusinessRankLogBasisWeb> rankLogBasisWebs) {
        return rankLogBasisWebs.stream()
                .collect(Collectors.toMap(
                        entity -> buildKey(entity.getIpAddress(), entity.getPort(), entity.getRankLogName()),
                        entity -> entity,
                        (existing, replacement) -> existing // 处理重复键的策略
                ));
    }

    private String buildKey(String ipAddress, String port, String rankLogName) {
        // 处理空值情况
        String ip = ipAddress != null ? ipAddress : "";
        String portStr = port != null ? port : "";
        String rankLogNameStr = rankLogName != null ? rankLogName : "";

        // 使用冒号分隔IP和端口
        return ip + ":" + portStr+ ":" + rankLogNameStr;
    }


    public static Map<String, List<AssetEntity>> groupByRemark(List<AssetEntity> assetList) {
        return assetList.stream()
                .collect(Collectors.groupingBy(
                        AssetEntity::getRemark,
                        Collectors.toList()
                ));
    }

    private List<String> convertAssetIdToList(String assetIdStr, String reportAssetType) {
        if (assetIdStr == null || assetIdStr.trim().isEmpty()) {
            return new ArrayList<>();
        }

        return Arrays.stream(assetIdStr.split(","))
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .map(assetId -> assetId + "|" + reportAssetType)
                .collect(Collectors.toList());
    }



    @Transactional(rollbackFor = Exception.class)
    public ComparisonResult compareAssets8(AssetImportService assetImportService) {
        // 1. 获取所有资产实体并按备注分组
        List<AssetEntity> assetEntities = assetImportService.getAssetEntities();
        //web
        List<WebAssetXlsx> webAssetList = assetImportService.getWebAssetList();
        Map<String, WebAssetXlsx> webAssetXlsxMap = webAssetList.stream().collect(Collectors.toMap(
                WebAssetXlsx::getDepartmentAssetCode,
                item -> item,
                (existing, replacement) -> existing
        ));
        //全部
        List<AssetReportZhiwang> assetReportZhiwangList = assetImportService.getAssetReportZhiwangList();
        Map<String, AssetReportZhiwang> assetReportZhiwangListMap = assetReportZhiwangList.stream()
                .collect(Collectors.toMap(
                        AssetIdGenerator::generateKey,
                        item -> item,
                        (existing, replacement) -> existing
                ));


        Map<String, List<AssetEntity>> groupedAssets = groupByRemark(assetEntities);

        // 准备存储所有SQL语句的Map，key为分类名称，value为SQL列表
        Map<String, List<String>> sqlCategories = new LinkedHashMap<>();

        // 2. 处理"已填报考核字段"分类
        processAssessmentFilledIn(groupedAssets, sqlCategories);

        // 3. 处理"补录考核字段"分类
        processSupplementaryRecording(groupedAssets, sqlCategories,webAssetXlsxMap);

        // 4. 处理"去年已上报删除"分类
        processHasBeenDeleted(groupedAssets, sqlCategories);

        // 5. 处理"去年已上报删除，台账仍填报"分类
        processHasBeenDeletedFill(groupedAssets, sqlCategories,assetReportZhiwangListMap);

        // 6. 处理"台账2已删除"分类
        processLedger2HasBeenDeleted(groupedAssets, sqlCategories);

        // 7. 处理"未录入ip"分类
        processIpAddressNotEntered(groupedAssets, sqlCategories);

        // 8. 将所有SQL写入文件
        writeSqlToFile(sqlCategories, "D:\\tool\\asset_operations2.sql");

        return null;
    }
    // 在AssetIdGenerator类中

    /**
     * 处理"已填报考核字段"分类
     */
    private void processAssessmentFilledIn(Map<String, List<AssetEntity>> groupedAssets,
                                           Map<String, List<String>> sqlCategories) {
        List<AssetEntity> assessmentFilledIn = groupedAssets.get("已填报考核字段");
        if (assessmentFilledIn == null || assessmentFilledIn.isEmpty()) return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 已填报考核字段操作开始");
        sqlList.add("-- ================================");

        // 获取资产ID列表
        List<String> ids = assessmentFilledIn.stream()
                .map(AssetEntity::getId)
                .collect(Collectors.toList());

        // 查询相关数据
        List<RankLogAssetInfoBasisWeb> basisWebs = businessRankMapper.selectRankLogAssetInfoBasisWebByIds(ids);
        List<RankLogAssetInfoSystemBasisWebCard> webCards = businessRankMapper.selectRankLogAssetInfoSystemBasisWebCard(ids);

        // 提取ID列表
        List<String> basisWebIds = basisWebs.stream()
                .map(RankLogAssetInfoBasisWeb::getId)
                .collect(Collectors.toList());
        List<String> weightWebIds = webCards.stream()
                .map(RankLogAssetInfoSystemBasisWebCard::getId)
                .collect(Collectors.toList());

        // 生成更新SQL
        sqlList.add("-- 更新表二(RankLogAssetInfoBasisWeb)数据");
        for (AssetEntity asset : assessmentFilledIn) {
            if (basisWebIds.contains(asset.getId())) {
                String sql = SqlGenerator.generateUpdateSqlWithTime(
                        asset.getId(), asset.getDivisionAssetId(), asset.getFoundTime());
                sqlList.add(sql);
            }
        }

        sqlList.add("-- 更新表三(RankLogAssetInfoSystemBasisWebCard)数据");
        for (AssetEntity asset : assessmentFilledIn) {
            if (weightWebIds.contains(asset.getId())) {
                String sql = SqlGenerator.generateUpdateSqlWithTimeWebCard(
                        asset.getId(), asset.getDivisionAssetId(), asset.getFoundTime());
                sqlList.add(sql);
            }
        }



        sqlList.add("-- ================================");
        sqlList.add("-- 已填报考核字段操作结束 sqlSize："+ sqlList.size());
        sqlList.add("-- ================================");
        sqlCategories.put("已填报考核字段", sqlList);
    }


    /**
     * 处理"补录考核字段"分类
     */
    private void processSupplementaryRecording(Map<String, List<AssetEntity>> groupedAssets,
                                               Map<String, List<String>> sqlCategories,
                                               Map<String, WebAssetXlsx> webAssetXlsxMap) {
        List<AssetEntity> hasBeenDeleted = groupedAssets.get("补录考核字段");
        if (hasBeenDeleted == null || hasBeenDeleted.isEmpty())  return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 补录考核字段 操作开始");
        sqlList.add("-- ================================");

            // 查询基础数据并转换为Map
        //关联查询主表，获取系统名称 下面的key增加定级备案名称
        List<BusinessRankLogBasisWeb> rankLogBasisWebs = businessRankMapper.selectBasisJoinWebList();
        Map<String, BusinessRankLogBasisWeb> basisWebMap = convertToIpPortMap(rankLogBasisWebs);

        // 生成插入SQL
        sqlList.add("-- 插入资产信息、IP端口信息和关联基础信息");

        ArrayList<String> unitType = new ArrayList<>();
        unitType.add("0701");
        unitType.add("0702");
        unitType.add("0703");
        // 查找web 文件中的资产信息， 根据资产id 使用url
        for (AssetEntity asset : hasBeenDeleted) {

            //新增uuid作为 BusinessRankLogBasisWeb表id，同时作为division_asset_info的real——id
            // 插入关联基础信息
            String key = asset.getAssetIp() + ":" + asset.getPorts()+ ":" + asset.getObjectName();
            String uuid = UUID.randomUUID().toString().replace("-", "");
            BusinessRankLogBasisWeb basisWeb = basisWebMap.get(key);
            if (basisWeb != null) {

                if (asset.getDivReportAssetType().equals("0699")){
                    WebAssetXlsx webAssetXlsx = webAssetXlsxMap.get(asset.getDivisionAssetId());
                    System.out.println("webAssetXlsx" + asset.getDivisionAssetId());
                    if (webAssetXlsx != null){
                        asset.setComponentName(webAssetXlsx.getUrl());
                    }

                }

                asset.setId(uuid);
                asset.setIpServce(basisWeb.getIpBusi());
                asset.setRelaId(basisWeb.getId());
                asset.setIpAccessControl(basisWeb.getIpAccessControl());
                asset.setCreateUser(basisWeb.getCreateUser());
                asset.setUpdateUser(basisWeb.getUpdateUser());
                asset.setUpdateUserName(basisWeb.getUpdateUserName());
                asset.setCreateUserName(basisWeb.getCreateUserName());
                sqlList.add(UniversalSqlGenerator.generateRankLogAssetInfoBasisWebInsertSql(asset,unitType));
            }

            asset.setRelaId(uuid);
            // 插入资产信息
            sqlList.add(UniversalSqlGenerator.generateDivisionAssetInfoInsertSql(asset));

            // 插入IP端口信息
            String[] ports = asset.getPorts().split(",");
            for (String port : ports) {
                sqlList.add(UniversalSqlGenerator.generateDivisionAssetIpPortInfoInsertSql(asset, port));
            }


        }
        sqlList.add("-- ================================");
        sqlList.add("-- 补录考核字段 操作结束 数量："+ sqlList.size());
        sqlList.add("-- ================================");
        sqlCategories.put("补录考核字段", sqlList);

    }


    /**
     * 处理"去年已上报删除"分类
     */
    private void processHasBeenDeleted(Map<String, List<AssetEntity>> groupedAssets,
                                       Map<String, List<String>> sqlCategories) {
        List<AssetEntity> hasBeenDeleted = groupedAssets.get("去年已上报删除");
        if (hasBeenDeleted == null || hasBeenDeleted.isEmpty()) return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 去年已上报删除操作开始");
        sqlList.add("-- ================================");

        sqlList.add("-- 插入资产信息和IP端口信息");
        for (AssetEntity asset : hasBeenDeleted) {
            // 插入资产信息
            sqlList.add(UniversalSqlGenerator.generateDivisionAssetInfoInsertSql(asset));

            // 插入IP端口信息
            String[] ports = asset.getPorts().split(",");
            for (String port : ports) {
                sqlList.add(UniversalSqlGenerator.generateDivisionAssetIpPortInfoInsertSql(asset, port));
            }
        }

        sqlList.add("-- ================================");
        sqlList.add("-- 去年已上报删除操作结束 sqlSize"+ sqlList.size() );
        sqlList.add("-- ================================");
        sqlCategories.put("去年已上报删除", sqlList);
    }

    /**
     * 处理"去年已上报删除，台账仍填报"分类
     * 000000CU07022023100801123
     * 000000CU 固定
     * 0702     资产类型  divReportAssetType
     * 20231008     年月日 foundTime
     * 01123    自增序列 从10000开始
     */
    private void processHasBeenDeletedFill(Map<String, List<AssetEntity>> groupedAssets,
                                           Map<String, List<String>> sqlCategories,
                                           Map<String, AssetReportZhiwang> assetReportZhiwangListMap) {
        List<AssetEntity> hasBeenDeletedFill = groupedAssets.get("去年已上报删除，台账仍填报");
        if (hasBeenDeletedFill == null || hasBeenDeletedFill.isEmpty()) return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 去年已上报删除，台账仍填报操作开始");
        sqlList.add("-- ================================");

        // 获取资产ID列表
        List<String> ids = hasBeenDeletedFill.stream()
                .map(AssetEntity::getId)
                .collect(Collectors.toList());

        // 查询相关数据
        List<RankLogAssetInfoBasisWeb> basisWebs = businessRankMapper.selectRankLogAssetInfoBasisWebByIds(ids);
        List<RankLogAssetInfoSystemBasisWebCard> webCards = businessRankMapper.selectRankLogAssetInfoSystemBasisWebCard(ids);

        // 提取ID列表
        List<String> basisWebIds = basisWebs.stream()
                .map(RankLogAssetInfoBasisWeb::getId)
                .collect(Collectors.toList());
        List<String> weightWebIds = webCards.stream()
                .map(RankLogAssetInfoSystemBasisWebCard::getId)
                .collect(Collectors.toList());

        //如果资产id为空的情况下 从全部表中根据ip+端口+资产类型+系统名称查找是否存在对应的  资产id 如果不存在则根据规则生成资产id
        // 生成更新SQL
        sqlList.add("-- 更新表二(RankLogAssetInfoBasisWeb)数据");
        for (AssetEntity asset : hasBeenDeletedFill) {
            if (basisWebIds.contains(asset.getId())) {
                if ( StringUtils.isBlank(asset.getDivisionAssetId())){
                    String key = asset.getAssetIp() + "_" + asset.getObjectName() + "_" + asset.getDivReportAssetType();
                    System.out.println("key = " + key);
                    AssetReportZhiwang assetReportZhiwang = assetReportZhiwangListMap.get(key);
                    if (assetReportZhiwang != null) {
                        System.out.println("找到全部文件中的对应数据 = " + assetReportZhiwang.getAssetId());
                        asset.setDivisionAssetId(assetReportZhiwang.getAssetId());
                    }else {
                        String assetId1 = AssetIdGenerator.generateAssetId(asset.getDivReportAssetType(), asset.getFoundTime());
                        System.out.println("未找到，自己生成 = " + assetId1);
                        asset.setDivisionAssetId(assetId1);
                    }
                }
                String sql = SqlGenerator.generateUpdateSqlWithTime(
                        asset.getId(), asset.getDivisionAssetId(), asset.getFoundTime());
                sqlList.add(sql);
            }
        }

        sqlList.add("-- 更新表三(RankLogAssetInfoSystemBasisWebCard)数据");
        for (AssetEntity asset : hasBeenDeletedFill) {
            if (weightWebIds.contains(asset.getId())) {
                String sql = SqlGenerator.generateUpdateSqlWithTimeWebCard(
                        asset.getId(), asset.getDivisionAssetId(), asset.getFoundTime());
                sqlList.add(sql);
            }
        }

        sqlList.add("-- ================================");
        sqlList.add("-- 去年已上报删除，台账仍填报操作结束 sqlSize" + sqlList.size());
        sqlList.add("-- ================================");
        sqlCategories.put("去年已上报删除，台账仍填报", sqlList);
    }

    /**
     * 处理"台账2已删除"分类
     */
    private void processLedger2HasBeenDeleted(Map<String, List<AssetEntity>> groupedAssets,
                                              Map<String, List<String>> sqlCategories) {
        List<AssetEntity> ledger2HasBeenDeleted = groupedAssets.get("台账2已删除");
        if (ledger2HasBeenDeleted == null || ledger2HasBeenDeleted.isEmpty()) return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 台账2已删除操作开始");
        sqlList.add("-- ================================");

        sqlList.add("-- 插入资产信息和IP端口信息");
        for (AssetEntity asset : ledger2HasBeenDeleted) {
            // 插入资产信息
            sqlList.add("-- 插入资产信息: " + asset.getId());
            sqlList.add(UniversalSqlGenerator.generateDivisionAssetInfoInsertSql(asset));

            // 插入IP端口信息
            sqlList.add("-- 插入IP端口信息: " + asset.getId());
            String[] ports = asset.getPorts().split(",");
            for (String port : ports) {
                sqlList.add(UniversalSqlGenerator.generateDivisionAssetIpPortInfoInsertSql(asset, port));
            }
        }

        sqlList.add("-- ================================");
        sqlList.add("-- 台账2已删除操作结束 sqlSize"+ sqlList.size());
        sqlList.add("-- ================================");
        sqlCategories.put("台账2已删除", sqlList);
    }

    /**
     * 处理"未录入ip"分类
     */
    private void processIpAddressNotEntered(Map<String, List<AssetEntity>> groupedAssets,
                                            Map<String, List<String>> sqlCategories) {
        List<AssetEntity> ipAddressNotEntered = groupedAssets.get("未录入ip");
        if (ipAddressNotEntered == null || ipAddressNotEntered.isEmpty()) return;

        List<String> sqlList = new ArrayList<>();
        sqlList.add("-- ================================");
        sqlList.add("-- 未录入ip操作开始");
        sqlList.add("-- ================================");

        sqlList.add("-- 插入资产信息和IP端口信息");
        for (AssetEntity asset : ipAddressNotEntered) {
            asset.setStatus("3");
            // 插入资产信息
//            sqlList.add("-- 插入资产信息: " + asset.getId());
            sqlList.add(UniversalSqlGenerator.generateDivisionAssetInfoInsertSql(asset));

            // 插入IP端口信息
//            sqlList.add("-- 插入IP端口信息: " + asset.getId());
            String[] ports = asset.getPorts().split(",");
            for (String port : ports) {
                sqlList.add(UniversalSqlGenerator.generateDivisionAssetIpPortInfoInsertSql(asset, port));
            }
        }

        sqlList.add("-- ================================");
        sqlList.add("-- 未录入ip操作结束 sqlSize" + sqlList.size());
        sqlList.add("-- ================================");
        sqlCategories.put("未录入ip", sqlList);
    }

    /**
     * 将SQL语句写入文件
     */
    private void writeSqlToFile(Map<String, List<String>> sqlCategories, String filePath) {
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 写入文件头注释
            writer.write("-- ===============================================");
            writer.newLine();
            writer.write("-- 资产操作SQL文件");
            writer.newLine();
            writer.write("-- 生成时间: " + new Date());
            writer.newLine();
            writer.write("-- ===============================================");
            writer.newLine();
            writer.newLine();

            // 按分类写入SQL
            for (Map.Entry<String, List<String>> entry : sqlCategories.entrySet()) {
                for (String sql : entry.getValue()) {
                    writer.write(sql);
                    writer.newLine();
                }
                writer.newLine(); // 分类之间空行分隔
            }

            System.out.println("SQL文件已成功生成: " + filePath);
        } catch (IOException e) {
            System.err.println("写入SQL文件失败: " + e.getMessage());
            e.printStackTrace();
        }
    }


    /**
     * 处理具体类型的资产列表
     */
    @SuppressWarnings("unchecked")
    private <T> int processAssetList(List<T> assetList, AssetTypeEnum assetType,
                                     Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap,
                                     ComparisonResult result,
                                     Map<String, String> domainMap,
                                     Map<String, AssetReportZhiwang> assetMap) {
        
        if (assetList == null || assetList.isEmpty()) {
            return 0;
        }
        
        System.out.println("开始处理 " + assetType.getTypeName() + " 类型资产，共 " + assetList.size() + " 条记录");
        
        int processedCount = 0;
        
        for (T asset : assetList) {
            try {
                DivisionAssetInfoWithIpPorts convertedAsset = convertToCommonAsset(asset, assetType,domainMap,assetMap);

                if (convertedAsset == null) {
                    result.addInvalidRecord("转换为通用资产对象失败", asset);
                    continue;
                }
                
                // 检查必要字段
                if (!isValidAssetRecord(convertedAsset)) {
                    result.addInvalidRecord("必要字段缺失（IP、端口或资产类型）", asset);
                    continue;
                }
                
                String matchKey = convertedAsset.createMatchKey();
                
                // 在数据库中查找匹配的记录
                if (databaseAssetMap.containsKey(matchKey)) {
                    DivisionAssetInfoWithIpPorts divisionAssetInfoWithIpPorts = databaseAssetMap.get(matchKey);
                    // 匹配成功，保留数据库中的旧数据
                    result.incrementMatchedCount();
                    // 创建更新记录
/*                    UpdateRecord updateRecord = new UpdateRecord();
                    updateRecord.setId(divisionAssetInfoWithIpPorts.getId());
                    updateRecord.setOldDivisionAssetId(divisionAssetInfoWithIpPorts.getDivisionAssetId());
                    updateRecord.setOldGroupAssetId(divisionAssetInfoWithIpPorts.getGroupAssetId());
                    updateRecord.setFoundTime(convertedAsset.getFoundTime()); // 使用Excel中的新发现时间
                    updateRecord.setDivisionAssetId(convertedAsset.getDivisionAssetId()); // 使用Excel中的新部测资产ID*/
                    convertedAsset.setInsertType("2");
                    convertedAsset.setOldDivisionAssetId(divisionAssetInfoWithIpPorts.getDivisionAssetId());
                    result.addUpdateRecord(convertedAsset);

                } else {
                    // Excel中有但数据库中无，需要插入
                    // 设置stamp为已删除状态（3）
//                    convertedAsset.setStatus(3); // 3-删除
                    convertedAsset.setInsertType("1");
                    result.addInsertRecord(convertedAsset);
                }
                
                processedCount++;
                
            } catch (Exception e) {
                System.err.println("处理资产记录时发生错误: " + e.getMessage());
                result.addInvalidRecord("处理过程中发生异常: " + e.getMessage(), asset);
            }
        }
        
        return processedCount;
    }



    private <T> int processAssetList2(List<T> assetList, AssetTypeEnum assetType,
                                      Map<String, DivisionAssetInfoWithIpPorts> databaseAssetMap,
                                      ComparisonResult result,
                                      Map<String, String> domainMap) {

        if (assetList == null || assetList.isEmpty()) {
            return 0;
        }

        System.out.println("开始处理 " + assetType.getTypeName() + " 类型资产，共 " + assetList.size() + " 条记录");

        int processedCount = 0;

        for (T asset : assetList) {
            try {
                DivisionAssetInfoWithIpPorts convertedAsset = convertToCommonAsset2(asset, assetType,domainMap);

                if (convertedAsset == null) {
                    result.addInvalidRecord("转换为通用资产对象失败", asset);
                    continue;
                }

         /*       // 检查必要字段
                if (!isValidAssetRecord(convertedAsset)) {
                    result.addInvalidRecord("必要字段缺失（IP、端口或资产类型）", asset);
                    result.addInvalidRecord("缺失类型", assetType.getTypeName());
                    continue;
                }
*/
//                String matchKey = convertedAsset.createMatchKey();

                convertedAsset.setInsertType("1");
                result.addInsertRecord(convertedAsset);

                processedCount++;

            } catch (Exception e) {
                System.err.println("处理资产记录时发生错误: " + e.getMessage());
                result.addInvalidRecord("处理过程中发生异常: " + e.getMessage(), asset);
            }
        }

        return processedCount;
    }

    private <T> DivisionAssetInfoWithIpPorts convertToCommonAsset2(Object asset, AssetTypeEnum assetType, Map<String, String> domainMap) {
        if (asset == null) {
            return null;
        }

        DivisionAssetInfoWithIpPorts commonAsset = new DivisionAssetInfoWithIpPorts();

        try {
            String remark = getFieldValue(asset, "remark", String.class);
            if (StringUtils.isBlank(remark) || !remark.equals("吴从后台删除导出的资产")){
                return null;
            }

            // 资产状态
            String assetStatus = getFieldValue(asset, "assetStatusAll", String.class);

            if (StringUtils.isBlank(assetStatus) || !assetStatus.equals("1")){
                return null;
            }
            commonAsset.setStatus(Integer.valueOf(assetStatus)); // 1-在网

            //部测资产id
            String departmentAssetCode = getFieldValue(asset, "assetId", String.class);
            commonAsset.setDivisionAssetId(departmentAssetCode);
            commonAsset.setGroupAssetId(departmentAssetCode);

            commonAsset.setIspCode(getFieldValue(asset, "operator", String.class));
            commonAsset.setOrgCode(getFieldValue(asset, "province", String.class));
            //是否可从互联网访问：0-否，1-是
            commonAsset.setIsAccess(Integer.valueOf(Objects.requireNonNull(getFieldValue(asset, "isAccessibleFromInternet", String.class))));
            commonAsset.setNetPosition(Integer.valueOf(Objects.requireNonNull(getFieldValue(asset, "networkLocation", String.class))));

            // 资产名称
            commonAsset.setAssetName( getFieldValue(asset, "assetName", String.class));



            // 获取上报资产类型
            String reportAssetType = getFieldValue(asset, "reportAssetType", String.class);
            commonAsset.setReportAssetType(reportAssetType);
            DeviceCategory deviceCategory = DeviceCategory.fromCategoryPath(reportAssetType);
            if (deviceCategory !=  null){
                String code = deviceCategory.getCode();
                commonAsset.setReportAssetType(code);
                //资产大类
                commonAsset.setAssetType(code.substring(0, 2));
            }



            //资产标签setAssetTag
            commonAsset.setAssetTag(getFieldValue(asset, "assetTag", String.class));

            String model = getFirstNonNull(
                    () -> getFieldValue(asset, "model", String.class),
                    // 数据库类型
                    () -> getFieldValue(asset, "databaseType", String.class),
                    // 中间件类型
                    () -> getFieldValue(asset, "middlewareType", String.class)
            );

            commonAsset.setAssetModel(model);

/*            String versionNumber = getFirstNonNull(
                    () -> getFieldValue(asset, "versionNumber", String.class),
                    () -> getFieldValue(asset, "databaseVersion", String.class),
                    () -> getFieldValue(asset, "middlewareVersion", String.class)
            );*/
            //版本号
            commonAsset.setAssetVersion(getFieldValue(asset, "version", String.class));




            //生产厂商
//            commonAsset.setManufacturer(getFieldValue(asset, "manufacturer", String.class));
            commonAsset.setBrandName(getFieldValue(asset, "brandNameCustomValueSimilarity", String.class));
            //品牌区域:0-国内，1-国外
            commonAsset.setBrandRegion(convertBrandRegion(getFieldValue(asset, "brandRegionCode", String.class)));


            //cpu生产厂商
            commonAsset.setCpuManufacturer(getFieldValue(asset, "cpuManufacturerCodeOrCustomValue", String.class));
            //CPU型号
            commonAsset.setCpuModel(getFieldValue(asset, "cpuModelCodeOrCustomValue", String.class));
            //内存生产商
            commonAsset.setMemoryManufacturer(getFieldValue(asset, "memoryManufacturerCodeOrCustomValue", String.class));
            //内存型号
            commonAsset.setMemoryModel(getFieldValue(asset, "memoryModelCodeOrCustomValue", String.class));

            commonAsset.setChipManufacturer(getFieldValue(asset, "switchChipManufacturerCodeOrCustomValue", String.class));
            commonAsset.setChipModel(getFieldValue(asset, "switchChipModelCodeOrCustomValue", String.class));

            // 是否采用开源框架：0-否，1-是
            commonAsset.setOpenSource(convertExpose(getFieldValue(asset, "isOpenSourceFrameworkCode", String.class)));

            commonAsset.setObejectName(getFieldValue(asset, "gradingObjectName", String.class));
            commonAsset.setSystemName(getFieldValue(asset, "businessSystemName", String.class));
            //定级等级
            commonAsset.setObejectLevel(Integer.valueOf(Objects.requireNonNull(getFieldValue(asset, "securityLevel", String.class))));

            //ip地址
            commonAsset.setAssetIp(getFieldValue(asset, "assetIp", String.class));
            String port = getFirstNonNull(
                    () -> getFieldValue(asset, "port", String.class),
                    () -> getFieldValue(asset, "portList", String.class),
                    () -> getFieldValue(asset, "openPort", String.class)
            );
            if (StringUtils.isBlank(port)){
                port = "65535";
            }
            commonAsset.setPorts(port);
            commonAsset.setFoundType(getFieldValue(asset, "assetDiscoveryMethod", String.class));
            String assetDiscoveryTime = getFieldValue(asset, "assetDiscoveryTime", String.class);
            // 定义格式：yyyyMMddHHmmss
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            // 验证并保持原格式
            String standard = DateUtils.formatToStandard(assetDiscoveryTime);
            Date date = sdf.parse(standard);
            commonAsset.setFoundTime(date);

            //虚拟机标识
            commonAsset.setNetworkUnitType(getFieldValue(asset, "isVirtualMachine", String.class));


            Boolean isVirtualMachine = convertBoolean(getFieldValue(asset, "isVirtualMachine", String.class));
            if (isVirtualMachine){
                DeviceCategory virtualMachine = DeviceCategory.VIRTUAL_MACHINE;
                commonAsset.setVirtualTag(virtualMachine.getName());
                commonAsset.setReportAssetType(virtualMachine.getCode());
            }
            commonAsset.setSourcepoolName("其他");



            // 处理布尔类型字段
            Boolean isOpenSource = convertBoolean(getFieldValue(asset, "isOpenSourceFramework", String.class));
            commonAsset.setOpenSource(isOpenSource ? 1 : 0);


            commonAsset.setCreateTime(new Date());
            commonAsset.setUpdateTime(new Date());

            return commonAsset;

        } catch (Exception e) {
            System.err.println("转换资产对象时发生错误: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 将不同类型的资产对象转换为通用的DivisionAssetInfoWithIpPorts对象
     */
    private DivisionAssetInfoWithIpPorts convertToCommonAsset(Object asset, AssetTypeEnum assetType, Map<String, String> domainMap, Map<String, AssetReportZhiwang> assetMap) {
        if (asset == null) {
            return null;
        }

        DivisionAssetInfoWithIpPorts commonAsset = new DivisionAssetInfoWithIpPorts();
        
        try {

            //部测资产id
            String departmentAssetCode = getFieldValue(asset, "departmentAssetCode", String.class);
            commonAsset.setDivisionAssetId(departmentAssetCode);
            commonAsset.setGroupAssetId(departmentAssetCode);

            commonAsset.setIspCode("CU");
            commonAsset.setOrgCode("000000");
            //是否可从互联网访问：0-否，1-是
            commonAsset.setIsAccess(convertExpose(getFieldValue(asset, "expose", String.class)));
            commonAsset.setNetPosition(convertNetPosition(getFieldValue(asset, "networkLocation", String.class)));

            // 资产名称
            commonAsset.setAssetName( getFieldValue(asset, "assetName", String.class));



            // 获取上报资产类型
            String reportAssetType = getFieldValue(asset, "reportAssetType", String.class);
            commonAsset.setReportAssetType(reportAssetType);

            if (reportAssetType != null){
                String codeByTypeName = ITResourceType.getCodeByTypeName(reportAssetType);
                commonAsset.setAssetType(codeByTypeName);

            }
            DeviceCategory deviceCategory = DeviceCategory.fromCategoryPath(reportAssetType);
            if (deviceCategory !=  null){
                String code = deviceCategory.getCode();
                commonAsset.setReportAssetType(code);
                //资产大类
                commonAsset.setAssetType(code.substring(0, 2));
            }

            // 资产状态
            String assetStatus = getFieldValue(asset, "assetStatus", String.class);
            commonAsset.setStatus(convertStatus(assetStatus)); // 1-在网

//            commonAsset.setStatus(3); // 删除



            String city = getFieldValue(asset, "city", String.class);
            String cityName = "";

            if (city != null && city.contains("/")) {
                String[] parts = city.split("/");
                cityName = parts[parts.length - 1]; // 取最后一个元素
            } else if (city != null) {
                cityName = city; // 如果没有分隔符，使用原字符串
            }
            //所在城市的code
            commonAsset.setLocation(domainMap.get(cityName));


            //资产标签setAssetTag
            commonAsset.setAssetTag(getFieldValue(asset, "assetTag", String.class));

            String model = getFirstNonNull(
                    () -> getFieldValue(asset, "model", String.class),
                    // 数据库类型
                    () -> getFieldValue(asset, "databaseType", String.class),
                    // 中间件类型
                    () -> getFieldValue(asset, "middlewareType", String.class)
            );

            commonAsset.setAssetModel(model);

            String versionNumber = getFirstNonNull(
                    () -> getFieldValue(asset, "versionNumber", String.class),
                    () -> getFieldValue(asset, "databaseVersion", String.class),
                    () -> getFieldValue(asset, "middlewareVersion", String.class)
            );
            //版本号
            commonAsset.setAssetVersion(versionNumber);




            //生产厂商
            commonAsset.setManufacturer(getFieldValue(asset, "manufacturer", String.class));
            commonAsset.setBrandName(getFieldValue(asset, "brandName", String.class));
            //品牌区域:0-国内，1-国外
            commonAsset.setBrandRegion(convertBrandRegion(getFieldValue(asset, "brandRegion", String.class)));


            //cpu生产厂商
            commonAsset.setCpuManufacturer(getFieldValue(asset, "cpuManufacturer", String.class));
            //CPU型号
            commonAsset.setCpuModel(getFieldValue(asset, "cpuModel", String.class));
            //内存生产商
            commonAsset.setMemoryManufacturer(getFieldValue(asset, "memoryManufacturer", String.class));
            //内存型号
            commonAsset.setMemoryModel(getFieldValue(asset, "memoryModel", String.class));

            commonAsset.setChipManufacturer(getFieldValue(asset, "switchChipManufacturer", String.class));
            commonAsset.setChipModel(getFieldValue(asset, "switchChipModel", String.class));

            // 是否采用开源框架：0-否，1-是
            commonAsset.setOpenSource(convertExpose(getFieldValue(asset, "isOpenSourceFramework", String.class)));

            commonAsset.setObejectName(getFieldValue(asset, "levelObjectName", String.class));
            commonAsset.setSystemName(getFieldValue(asset, "businessSystem", String.class));
            //定级等级
            commonAsset.setObejectLevel(extractLevel(getFieldValue(asset, "securityLevel", String.class)));

            //ip地址
            commonAsset.setAssetIp(getFieldValue(asset, "assetIp", String.class));
            String port = getFirstNonNull(
                    () -> getFieldValue(asset, "port", String.class),
                    () -> getFieldValue(asset, "portList", String.class),
                    () -> getFieldValue(asset, "openPort", String.class)
            );
            if (StringUtils.isBlank(port)){
                port = "65535";
            }
            commonAsset.setPorts(port);

            AssetReportZhiwang assetReportZhiwang = assetMap.get(departmentAssetCode);
            if (assetReportZhiwang != null){
                if (assetReportZhiwang.getAssetStatusAll().equals("3")){
                    System.out.println("状态为3跳过循环 = " + assetReportZhiwang.getInstructionId());
                    return null;
                }

                commonAsset.setFoundType(assetReportZhiwang.getAssetDiscoveryMethod());

                String assetDiscoveryTime = assetReportZhiwang.getAssetDiscoveryTime();

                // 定义格式：yyyyMMddHHmmss
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
                try {
                    // 验证并保持原格式
                    String standard = DateUtils.formatToStandard(assetDiscoveryTime);
                    Date date = sdf.parse(standard);
                    commonAsset.setFoundTime(date);
                    // 输出示例: Fri May 12 11:09:35 CST 2023
                } catch (ParseException e) {
                    System.err.println("日期解析错误: " + e.getMessage());
                }

            }


            //虚拟机标识
            commonAsset.setNetworkUnitType(getFieldValue(asset, "isVirtualMachine", String.class));


            Boolean isVirtualMachine = convertBoolean(getFieldValue(asset, "isVirtualMachine", String.class));
            if (isVirtualMachine){
                DeviceCategory virtualMachine = DeviceCategory.VIRTUAL_MACHINE;
                commonAsset.setVirtualTag(virtualMachine.getName());
                commonAsset.setReportAssetType(virtualMachine.getCode());
            }
            commonAsset.setSourcepoolName("其他");



            // 处理布尔类型字段
            Boolean isOpenSource = convertBoolean(getFieldValue(asset, "isOpenSourceFramework", String.class));
            commonAsset.setOpenSource(isOpenSource ? 1 : 0);


            commonAsset.setCreateTime(new Date());
            commonAsset.setUpdateTime(new Date());
            
            return commonAsset;
            
        } catch (Exception e) {
            System.err.println("转换资产对象时发生错误: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    public static <T> T getFirstNonNull(Supplier<T>... suppliers) {
        for (Supplier<T> supplier : suppliers) {
            T value = supplier.get();
            if (value != null && !(value instanceof String) || StringUtils.isNotBlank((String) value)) {
                return value;
            }
        }
        return null;
    }



    // 辅助方法：转换状态值
    private Integer convertStatus(String statusStr) {
        if (statusStr == null) return 1; // 默认在网
        switch (statusStr) {
            case "在网": return 1;
            case "下线": return 0;
            case "停用": return 2;
            case "删除": return 3;
            default: return 1;
        }
    }
    private Integer convertNetPosition(String statusStr) {
        if (statusStr == null) return 0; // 默认内网
        switch (statusStr) {
            case "外网": return 1;
            case "内网": return 0;
            default: return 0;
        }
    }
    private Integer convertExpose(String statusStr) {
        if (statusStr == null) return 0; // 默认在网
        switch (statusStr) {
            case "是": return 1;
            case "否": return 0;
            default: return 0;
        }
    }

    private Boolean convertBoolean(String statusStr) {
        if (statusStr == null) return false;
        switch (statusStr) {
            case "是": return true;
            case "否": return false;
            default: return false;
        }
    }

    private Integer convertBrandRegion(String brandRegion) {
        if (brandRegion == null) return 0;
        switch (brandRegion) {
            case "国内": return 0;
            case "国外": return 1;
            default: return 0;
        }
    }

    public static Integer extractLevel(String levelStr) {
        if (levelStr == null || levelStr.trim().isEmpty()) {
            return null;
        }

        // 使用正则表达式匹配连续的数字
        Pattern pattern = Pattern.compile("\\d+");
        Matcher matcher = pattern.matcher(levelStr);

        if (matcher.find()) {
            try {
                return Integer.parseInt(matcher.group());
            } catch (NumberFormatException e) {
                return null;
            }
        }
        return null;
    }


    /**
     * 使用反射获取对象字段的值
     */
    @SuppressWarnings("unchecked")
    private <T> T getFieldValue(Object obj, String fieldName, Class<T> returnType) {
        try {
            java.lang.reflect.Field field = obj.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            Object value = field.get(obj);
            
            if (value == null) {
                return null;
            }
            
            if (returnType.isAssignableFrom(value.getClass())) {
                return (T) value;
            } else if (returnType == String.class) {
                return (T) value.toString();
            }
            
            return null;
        } catch (NoSuchFieldException | IllegalAccessException e) {
            // 字段不存在或无法访问，返回null
            return null;
        }
    }
    
    /**
     * 检查资产记录是否有效（包含必要的匹配字段）
     */
    private boolean isValidAssetRecord(DivisionAssetInfoWithIpPorts asset) {
        return asset != null && 
               asset.getAssetIp() != null && !asset.getAssetIp().trim().isEmpty() &&
               asset.getPorts() != null && !asset.getPorts().trim().isEmpty() &&
               asset.getReportAssetType() != null && !asset.getReportAssetType().trim().isEmpty();
    }

    /**
     * 生成SQL插入语句
     * 为division_asset_info和division_asset_ip_port_info两张表生成插入语句
     */
    public List<String> generateInsertSqlStatements(ComparisonResult result) {
        List<String> sqlStatements = new ArrayList<>();

        for (DivisionAssetInfoWithIpPorts asset : result.getInsertRecords()) {
            // 1. 生成division_asset_info表的插入语句
            StringBuilder assetSql = new StringBuilder();
            assetSql.append("INSERT INTO division_asset_info (");
            assetSql.append("rela_id, group_asset_id, division_asset_id, isp_code, org_code, ");
            assetSql.append("is_access, net_position, asset_name, asset_type, report_asset_type, ");
            assetSql.append("status, location, asset_tag, asset_model, asset_version, manufacturer, ");
            assetSql.append("brand_name, brand_region, cpu_manufacturer, cpu_model, ");
            assetSql.append("memory_manufacturer, memory_model, chip_manufacturer, chip_model, ");
            assetSql.append("open_source, open_source_model, open_source_verison, system_name, ");
            assetSql.append("obeject_name, obeject_level, network_unit_type, found_type, found_time, ");
            assetSql.append("virtual_tag, sourcepool_name, create_time, update_time, important, ");
            assetSql.append("network_keypoint, subdivision_type, manage_mode, `usage`, manager_name, ");
            assetSql.append("manager_mobile");
            assetSql.append(") VALUES (");

            // 添加division_asset_info表的值
            assetSql.append(asset.getRelaId() != null ? "'" + escapeSql(asset.getRelaId()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getGroupAssetId() != null ? "'" + escapeSql(asset.getGroupAssetId()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getDivisionAssetId() != null ? "'" + escapeSql(asset.getDivisionAssetId()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getIspCode() != null ? "'" + escapeSql(asset.getIspCode()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getOrgCode() != null ? "'" + escapeSql(asset.getOrgCode()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getIsAccess() != null ? asset.getIsAccess() : "NULL").append(", ");
            assetSql.append(asset.getNetPosition() != null ? asset.getNetPosition() : "NULL").append(", ");
            assetSql.append(asset.getAssetName() != null ? "'" + escapeSql(asset.getAssetName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getAssetType() != null ? "'" + escapeSql(asset.getAssetType()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getReportAssetType() != null ? "'" + escapeSql(asset.getReportAssetType()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getStatus() != null ? asset.getStatus() : "NULL").append(", ");
            assetSql.append(asset.getLocation() != null ? "'" + escapeSql(asset.getLocation()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getAssetTag() != null ? "'" + escapeSql(asset.getAssetTag()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getAssetModel() != null ? "'" + escapeSql(asset.getAssetModel()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getAssetVersion() != null ? "'" + escapeSql(asset.getAssetVersion()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getManufacturer() != null ? "'" + escapeSql(asset.getManufacturer()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getBrandName() != null ? "'" + escapeSql(asset.getBrandName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getBrandRegion() != null ? asset.getBrandRegion() : "NULL").append(", ");
            assetSql.append(asset.getCpuManufacturer() != null ? "'" + escapeSql(asset.getCpuManufacturer()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getCpuModel() != null ? "'" + escapeSql(asset.getCpuModel()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getMemoryManufacturer() != null ? "'" + escapeSql(asset.getMemoryManufacturer()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getMemoryModel() != null ? "'" + escapeSql(asset.getMemoryModel()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getChipManufacturer() != null ? "'" + escapeSql(asset.getChipManufacturer()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getChipModel() != null ? "'" + escapeSql(asset.getChipModel()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getOpenSource() != null ? asset.getOpenSource() : "NULL").append(", ");
            assetSql.append(asset.getOpenSourceModel() != null ? "'" + escapeSql(asset.getOpenSourceModel()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getOpenSourceVerison() != null ? "'" + escapeSql(asset.getOpenSourceVerison()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getSystemName() != null ? "'" + escapeSql(asset.getSystemName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getObejectName() != null ? "'" + escapeSql(asset.getObejectName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getObejectLevel() != null ? asset.getObejectLevel() : "NULL").append(", ");
            assetSql.append(asset.getNetworkUnitType() != null ? "'" + escapeSql(asset.getNetworkUnitType()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getFoundType() != null ? "'" + escapeSql(asset.getFoundType()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getFoundTime() != null ? "'" + escapeSql(asset.getFoundTime().toString()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getVirtualTag() != null ? "'" + escapeSql(asset.getVirtualTag()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getSourcepoolName() != null ? "'" + escapeSql(asset.getSourcepoolName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getCreateTime() != null ? "'" + escapeSql(asset.getCreateTime().toString()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getUpdateTime() != null ? "'" + escapeSql(asset.getUpdateTime().toString()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getImportant() != null ? asset.getImportant() : "NULL").append(", ");
            assetSql.append(asset.getNetworkKeypoint() != null ? "'" + escapeSql(asset.getNetworkKeypoint()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getSubdivisionType() != null ? "'" + escapeSql(asset.getSubdivisionType()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getManageMode() != null ? "'" + escapeSql(asset.getManageMode()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getUsage() != null ? "'" + escapeSql(asset.getUsage()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getManagerName() != null ? "'" + escapeSql(asset.getManagerName()) + "'" : "NULL").append(", ");
            assetSql.append(asset.getManagerMobile() != null ? "'" + escapeSql(asset.getManagerMobile()) + "'" : "NULL");
            assetSql.append(");");

            sqlStatements.add(assetSql.toString());

            // 2. 生成division_asset_ip_port_info表的插入语句
            if (asset.getAssetIp() != null && asset.getPorts() != null && !asset.getPorts().isEmpty()) {
                // 拆分端口字符串
                String[] portArray = asset.getPorts().split(",");

                for (String port : portArray) {
                    StringBuilder ipPortSql = new StringBuilder();
                    ipPortSql.append("INSERT INTO division_asset_ip_port_info (");
                    ipPortSql.append("group_asset_id, division_asset_id, asset_ip, port, ip_type, ");
                    ipPortSql.append("is_access, manage_tag, net_position");
                    ipPortSql.append(") VALUES (");

                    // 添加division_asset_ip_port_info表的值
                    ipPortSql.append(asset.getGroupAssetId() != null ? "'" + escapeSql(asset.getGroupAssetId()) + "'" : "NULL").append(", ");
                    ipPortSql.append(asset.getDivisionAssetId() != null ? "'" + escapeSql(asset.getDivisionAssetId()) + "'" : "NULL").append(", ");
                    ipPortSql.append("'" + escapeSql(asset.getAssetIp()) + "', ");
                    ipPortSql.append(port.trim()).append(", ");
                    ipPortSql.append(asset.getIpType() != null ? asset.getIpType() : "NULL").append(", ");
                    ipPortSql.append(asset.getIsAccess() != null ? asset.getIsAccess() : "NULL").append(", ");
                    ipPortSql.append(asset.getManageTag() != null ? asset.getManageTag() : "NULL").append(", ");
                    ipPortSql.append(asset.getNetPosition() != null ? asset.getNetPosition() : "NULL");
                    ipPortSql.append(");");

                    sqlStatements.add(ipPortSql.toString());
                }
            }
        }

        return sqlStatements;
    }

    // 辅助方法：转义SQL特殊字符
    private String escapeSql(String str) {
        if (str == null) {
            return "";
        }
        return str.replace("'", "''");
    }
}